/**
	Name                :  Stephen Durfey
	Course Number: CS462
	Semester         :  Spring 2009
	Assignment      : 7
	Due Date          : Friday, April 3rd, 12:00pm
	
	Description: This program is a simple database application that will perform specified select statements on a set of three relations
			sp46212.DEPARTMENT, sp46212.INTERSECTION, and sp46212.FACULTY. The user will be able to select what type of query
			they would like to perform, they have two options: a query on the department, which will return a list of all faculty members in
			that department (if any), and a query on a particular faculty member, which will return all departments associated with that 
			faculty member. If no results are found, the user is notified.
*/

#include <iostream>
#include <string>
#include <iomanip>
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <unistd.h>

using namespace std;

//Declare Section

EXEC SQL BEGIN DECLARE SECTION;

	VARCHAR   username[20];
	VARCHAR   password[20];
	VARCHAR   sqlStatement[800];
EXEC SQL END DECLARE SECTION;

	struct readData
	{
		EXEC SQL BEGIN DECLARE SECTION;
		int       DS_DeptNum;
		VARCHAR   DS_DeptName[30];
		VARCHAR   DS_DeptLocation[30];
		VARCHAR   DS_DeptPhone[12];
	
		int       DS_FacNum;
		int       DS_FacYearStart;
		VARCHAR   DS_FacName[20];
		VARCHAR   DS_FacRank[4];
	
		int       DS_IDeptNum;
		int       DS_IFacNum;
		VARCHAR   DS_IOffice[30];
		VARCHAR   DS_IPhone[12];
		EXEC SQL END DECLARE SECTION;
	} dataRead;

//include SQL communications area

EXEC SQL INCLUDE SQLCA;

//function declarations

void connect();  //connects to Oracle
void buildSqlSelectDepartment(); //builds specified sql statement for a department query
void buildSqlSelectFaculty();    //builds specified sql statement for a faculty member query
void prepareExecute( string queryType ); //creates cursor and executes sql statement
void printResultsDepartment( int &counter ); //prints out results from select statement on a department
void printResultsFaculty( int &counter ); //prints out results from select statement on a faculty member
void commit(); //severs Oracle connection
void sqlErrorHandle(char *); //handles errors in SQL code
void notFound( int counter ); //called whenver the select statement doesnt return any tuples

int main()
{
	int decision = 0;
	
	//defines which function to call in case of an SQL error
	EXEC SQL WHENEVER SQLERROR do sqlErrorHandle("***Oracle Error:  ");
	
	//connects to DB
	connect();
	
	cout << "Welcome to the interoffice database search application!\n";
	cout << "Specify a search type below, please\n\n";
	
	//displays menu for user to select options from, continues to loop until the user presses '3'
	//to exit the proram, which will call the commit function
	do
	{
		cout  << "(1) Department Query     (lists all faculty members within specified department number)\n";
		cout  << "(2) Faculty Member Query (lists all departments the specified employee number is associated with\n";
		cout  << "(3) Exit\n";
		cout  << "Enter choice: ";
		cin   >> decision;

		if( decision == 1 )
		{
			cout << "\n\nEnter Department Number: ";
			cin  >> department;
			buildSqlSelectDepartment( department );

			prepareExecute( "department" );
		}
		else if( decision == 2 )
		{
			cout << "\n\nEnter Faculty Number: ";
			cin  >> facultyNum;
			buildSqlSelectFaculty( facultyNum );

			prepareExecute( "faculty" );
		}
		
		if( decision != 1 && decision != 2 && decision != 3 )
		{
			cout << "I am sorry, I cannot process your request, please select the correct option from the menu, thank you\n\n\n";
		}
		
	}
	while( decision != 3 );
	
	commit();

	return 0;
}//end of main function

//asks for username and password, and attempts to connect to Oracle
void connect()
{
	//gathers the Oracle username and password
	char* temp_password;

	cout << "Enter Oracle Username: ";
	cin >> username.arr;

	temp_password = getpass("Enter Oracle Password: ");
	strcpy((char*)password.arr, temp_password);

	//gets length of username and password
	username.len = strlen( (char*) username.arr );
	password.len = strlen( (char*) password.arr );

	//connects to Oracle, with the specified username and password
	EXEC SQL CONNECT :username IDENTIFIED BY :password;

	cout << "Connected to Oracle as user: " << (char*) username.arr << endl << endl;

}//end of connect function


//builds the SQL select query, each time the function is called, the user is prompted to enter a  department number, which is then
//catatenated onto the end of the SELECT statement, and subsequently printed to the screen for your viewing pleasure.
void buildSqlSelectDepartment( string department )
{
	

	strcpy( (char*) sqlStatement.arr,  "SELECT   INTERSECTION.IDeptNum, DEPARTMENT.DeptName,\n"
									   "         DEPARTMENT.DeptLocation, INTERSECTION.IFacNum,\n"
									   "         FACULTY.FacName, INTERSECTION.IOffice,\n"
									   "         INTERSECTION.IPhone, FACULTY.FacRank, FACULTY.FacYearStart\n"
									   "FROM     DEPARTMENT\n"
									   "JOIN     INTERSECTION\n"
									   "ON       DEPARTMENT.DeptNum = INTERSECTION.IDeptNum\n"
									   "JOIN     FACULTY\n"
									   "ON       INTERSECTION.IFacNum = FACULTY.FacNum\n"
									   "WHERE    DEPARTMENT.DeptNum = ");

	strcat( (char*) sqlStatement.arr, department.c_str() );
								
	cout << "Query is:  \n" << sqlStatement.arr << endl; 
}//end of buildSqlSelectDepartment function

//builds the SQL select query, each time the function is called, the user is prompted to enter a faculty member number, which is then
//catatenated onto the end of the SELECT statement, and subsequently printed to the screen for your viewing pleasure.
void buildSqlSelectFaculty( string facultyNum )
{
	strcpy( (char*) sqlStatement.arr,  "SELECT    INTERSECTION.IDeptNum,  DEPARTMENT.DeptName,\n"
									   "          DEPARTMENT.DeptLocation,  INTERSECTION.IFacNum,\n"
									   "          FACULTY.FacName,  INTERSECTION.IOffice,\n"
									   "          INTERSECTION.IPhone,  FACULTY.FacRank,  FACULTY.FacYearStart\n"
									   "FROM      FACULTY\n"
									   "JOIN      INTERSECTION\n"
									   "ON        FACULTY.FacNum =  INTERSECTION.IFacNum\n"
									   "JOIN      DEPARTMENT\n"
									   "ON        INTERSECTION.IDeptNum =  DEPARTMENT.DeptNum\n"
									   "WHERE     FACULTY.FacNum = ");

	strcat( (char*) sqlStatement.arr, facultyNum.c_str() );
								
	cout << "Query is:  \n" << sqlStatement.arr << endl;
} //end of buildSqlSelectFaculty function

void prepareExecute( string queryType )
{
	int counter = 0; //keeps track on number of iterations, used in three functions, notFound, printResultsDepartment, and printResultsFaculty
	
	sqlStatement.len = strlen( (char*) sqlStatement.arr );

	//prepares SQL select statement
	
	EXEC SQL PREPARE S1 FROM :sqlStatement;

	//associate cursor with a prepared sql statement
	
	EXEC SQL DECLARE C1 CURSOR FOR S1;

	//open the cursor, execute SQL statement, place tuples in the cursor
	EXEC SQL OPEN C1;
	
	//loops through the cursor, and extracts the data, and stores that data into each variable in the
	//declare section, as specified 
	do
	{
		//if zero tuples were selected from DB, call the function notFound
		EXEC SQL WHENEVER NOT FOUND do notFound( counter );
		EXEC SQL FETCH C1 INTO  dataRead.DS_IDeptNum,
								dataRead.DS_DeptName,
								dataRead.DS_DeptLocation,
								dataRead.DS_IFacNum,
								dataRead.DS_FacName,
								dataRead.DS_IOffice,
								dataRead.DS_IPhone,
								dataRead.DS_FacRank,
								dataRead.DS_FacYearStart;

		if( sqlca.sqlcode == 0 )
		{
			if( queryType == "department" )
				printResultsDepartment( counter );
			else
				printResultsFaculty( counter );
		}
			
		counter++;
		
	}
	while( sqlca.sqlcode == 0 ); //end of retrieve do-while loop
	
	cout << endl << endl;
	counter = 0;
	
}//end of prepareExecute function

//called and printed if no rows were selected from the database
//if counter is equal to 0, then it means this function was called during the 
//first iteration of the do-while loop in the previous function, prepareExecute()
void notFound( int counter )
{
	if( counter == 0 )
		cout << "\n\nNo tuples found matching the requested statement\n\n";
}//end of notFound function

//prints the results of each department, by listing all faculty memebers of that department
 void printResultsDepartment( int &counter )
 {
	//prints header information for department being queried
	if( counter == 0 )
	{
		cout << "\n\n\n---------------------------------- Department -----------------------------------\n";
		
		cout << "               Depart Num :  " <<  dataRead.DS_IDeptNum                 << endl;
		cout << "               Depart Name:  " << (char*)dataRead.DS_DeptName.arr      << endl;
		cout << "               Location   :  " << (char*)dataRead.DS_DeptLocation.arr  << endl;       
		
		cout << "----------------------------------- Faculty Member(s) ---------------------------------\n\n";
		cout << "    Name             Num         Office            Phone         Rank      Start Year\n";
	    cout << "============        =====   =================  =============    ========  ===========\n\n";
	}//end of print header if statement
	
	//prints specified variables in a uniform and formatted way
	cout << setw(20) << left << (char *)dataRead.DS_FacName.arr;
	cout << setw(8)  << left << dataRead.DS_IFacNum;
	cout << setw(19) << left << (char *)dataRead.DS_IOffice.arr;
	cout << setw(17) << left << (char *)dataRead.DS_IPhone.arr; 
	cout << setw(10) << left << (char *)dataRead.DS_FacRank.arr;
	cout << setw(10) << left << dataRead.DS_FacYearStart << endl;
	
	
	strcpy( (char *)dataRead.DS_DeptName.arr,     "                 " );
	strcpy( (char *)dataRead.DS_DeptLocation.arr, "                 " );
	strcpy( (char *)dataRead.DS_IOffice.arr,      "                 " );
	strcpy( (char *)dataRead.DS_FacName.arr,      "                 " );
	strcpy( (char *)dataRead.DS_IPhone.arr,       "                 " );
	strcpy( (char *)dataRead.DS_FacRank.arr,      "                 " );

 } //end print deparment function
 
 //prints results of each faculty memeber specified, by printing out the department information
 //for each department the faculty member is a member of.
 void printResultsFaculty( int &counter )
 {
	//prints header only once. the counter variable ensures this
	if( counter == 0 )
	{
		cout << "\n\n\n-------------------------- Faculty Member ------------------------------\n";
		
		cout << "               Faculty Num:  " <<  dataRead.DS_IFacNum                 << endl;
		cout << "               Name       :  " << (char *)dataRead.DS_FacName.arr       << endl;
		cout << "               Rank       :  " << (char *)dataRead.DS_FacRank.arr       << endl;
		cout << "               Year Start :  " <<  dataRead.DS_FacYearStart             << endl;       
		
		cout << "---------------------------- Department(s)  ----------------------------\n\n";
		cout << "  Num      Department Name           Office            Location           Phone\n";
		cout << " =====   ===================    ================    ==============     =============\n";
	}//end print header if statment
	
	//prints output from variables in the declare section
	cout << "   " << setw(8)   << left << dataRead.DS_IDeptNum;
	cout << setw(22)  << left << (char *)dataRead.DS_DeptName.arr;
	cout << setw(19)  << left << (char *)dataRead.DS_IOffice.arr;
	cout << setw(20)  << left << (char *)dataRead.DS_DeptLocation.arr; 
	cout << setw(10)  << left << (char *)dataRead.DS_IPhone.arr << endl;

	strcpy( (char *)dataRead.DS_DeptName.arr,     "                 " );
	strcpy( (char *)dataRead.DS_DeptLocation.arr, "                 " );
	strcpy( (char *)dataRead.DS_IOffice.arr,      "                 " );
	strcpy( (char *)dataRead.DS_FacName.arr,      "                 " );
	strcpy( (char *)dataRead.DS_IPhone.arr,       "                 " );
	strcpy( (char *)dataRead.DS_FacRank.arr,      "                 " );

 } //end print faculty function
 
//commits work performed on database and severs connection
void commit()
{
	EXEC SQL COMMIT WORK RELEASE;
}//end commit function

//error handler used everytime an SQL error is generated in Oracle, could be an incorrect login name/password
//or an error in SQL syntax
void sqlErrorHandle( char * message )
{
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	
	cout << endl << message << endl;
	cout << sqlca.sqlerrm.sqlerrmc << endl;
	
	EXEC SQL ROLLBACK RELEASE;
	
	exit(1);
} //end sqlError function
